﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Collections.Concurrent;
using System.Reflection.Emit;
using System.Threading;
using System.Runtime.CompilerServices;
#pragma warning disable 1573, 1591 // xml comments
using Dapper;
using System.Text.RegularExpressions;


using hentity;
using hutil;

namespace Dapper
{
    public static class SqlMapperExtensions
    {
        public interface IProxy
        {
            bool IsDirty { get; set; }
        }
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>();
		private static readonly ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>> ComputedProperties = new ConcurrentDictionary<RuntimeTypeHandle, IEnumerable<PropertyInfo>>(); 
        private static readonly ConcurrentDictionary<RuntimeTypeHandle, string> GetQueries = new ConcurrentDictionary<RuntimeTypeHandle, string>();
    
		private static readonly Dictionary<string, ISqlAdapter> AdapterDictionary = new Dictionary<string, ISqlAdapter>() {
																							{"sqlconnection", new SqlServerAdapter()},
																							{"npgsqlconnection", new PostgresAdapter()},
																							{"sqliteconnection", new SQLiteAdapter()}
																						};
		private static IEnumerable<PropertyInfo> ComputedPropertiesCache(Type type)
		{
			IEnumerable<PropertyInfo> pi;
			if (ComputedProperties.TryGetValue(type.TypeHandle, out pi))
			{
				return pi;
			}
			var computedProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ComputedAttribute)).ToList();
			ComputedProperties[type.TypeHandle] = computedProperties;
			return computedProperties;
		}
        private static IEnumerable<PropertyInfo> KeyPropertiesCache(Type type)
        {
            IEnumerable<PropertyInfo> pi;
            if (KeyProperties.TryGetValue(type.TypeHandle,out pi))
            {
                return pi;
            }
            var allProperties = TypePropertiesCache(type);
            var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();
            if (keyProperties.Count == 0)
            {
                var idProp = allProperties.Where(p => p.Name.ToLower() == "id").FirstOrDefault();
                if (idProp != null)
                {
                    keyProperties.Add(idProp);
                }
            }
            KeyProperties[type.TypeHandle] = keyProperties;
            return keyProperties;
        }
        private static IEnumerable<PropertyInfo> TypePropertiesCache(Type type)
        {
            IEnumerable<PropertyInfo> pis;
            if (TypeProperties.TryGetValue(type.TypeHandle, out pis))
            {
                return pis;
            }
            var properties = type.GetProperties().Where(IsWriteable).ToArray();
            TypeProperties[type.TypeHandle] = properties;
            return properties;
        }
		public static bool IsWriteable(PropertyInfo pi)
		{
			object[] attributes = pi.GetCustomAttributes(typeof (WriteAttribute), false);
			if (attributes.Length == 1)
			{
				WriteAttribute write = (WriteAttribute) attributes[0];
				return write.Write;
			}
			return true;
		}
    	/// <summary>
        /// Returns a single entity by a single id from table "Ts". T must be of interface type. 
        /// Id must be marked with [Key] attribute.
        /// Created entity is tracked/intercepted for changes and used by the Update() extension. 
        /// </summary>
        /// <typeparam name="T">Interface type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <returns>Entity of T</returns>
        public static T Get<T>(this IDbConnection connection, dynamic id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            string sql;
            if (!GetQueries.TryGetValue(type.TypeHandle, out sql))
            { 
                var keys = KeyPropertiesCache(type);
                if (keys.Count() > 1)
                    throw new DataException("Get<T> only supports an entity with a single [Key] property");
                if (keys.Count() == 0)
                    throw new DataException("Get<T> only supports en entity with a [Key] property");
                var onlyKey = keys.First();
                var name = ToStr.GetTableName(type);
                // TODO: pluralizer 
                // TODO: query information schema and only select fields that are both in information schema and underlying class / interface 
                sql = "select * from " + name + " where " + onlyKey.Name + " = @id";
                GetQueries[type.TypeHandle] = sql;
            }
            var dynParms = new DynamicParameters();
            dynParms.Add("@id", id);
            T obj = null;
            if (type.IsInterface)
            {
                var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary<string, object>;
                if (res == null)
                    return (T)((object)null);
                obj = ProxyGenerator.GetInterfaceProxy<T>();
                foreach (var property in TypePropertiesCache(type))
                {
                    var val = res[property.Name];
                    property.SetValue(obj, val, null);
                }
                ((IProxy)obj).IsDirty = false;   //reset change tracking and return
            }
            else
            {
                obj = connection.Query<T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
            }
            return obj;
        }
        static Dictionary<Type ,string> idQueryDict = new Dictionary<Type ,string>();
        public static T GetById<T>(this IDbConnection connection, long id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            if(! idQueryDict.ContainsKey(type) ) {
                var name = ToStr.GetTableName(type);
                idQueryDict.Add(type ,"select * from " + name + " where id = @id");
            } 
            string sql = idQueryDict[type];
             var dynParms = new DynamicParameters();
            dynParms.Add("@id", id);
            T obj = null;
            if (type.IsInterface)
            {
                var res = connection.Query(sql, dynParms).FirstOrDefault() as IDictionary<string, object>;
                if (res == null)
                    return (T)((object)null);
                obj = ProxyGenerator.GetInterfaceProxy<T>();
                foreach (var property in TypePropertiesCache(type))
                {
                    var val = res[property.Name];
                    property.SetValue(obj, val, null);
                }
                ((IProxy)obj).IsDirty = false;   //reset change tracking and return
            }
            else
            {
                obj = connection.Query<T>(sql, dynParms, transaction: transaction, commandTimeout: commandTimeout).FirstOrDefault();
            }
            return obj;
        }
        /// <summary>
        /// Returns a single entity by a single id from table "Ts". T must be of interface type. 
        /// Id must be marked with [Key] attribute.
        /// Created entity is tracked/intercepted for changes and used by the Update() extension. 
        /// </summary>
        /// <typeparam name="T">Interface type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <returns>Entity of T</returns>
        /// 
         public static List<T> GetAll<T>(this IDbConnection connection, string whereStr=null,object param = null, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
         
            return getField<T>(connection,"*" , whereStr  , param  , transaction  , commandTimeout );
        }
         public static List<T> getField<T>(this IDbConnection connection ,String field ,string whereStr = null ,object param = null ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            string sql = "select "+ field + " from " + name + " where 1=1 " + whereStr;
            return connection.Query<T>(sql ,param ,transaction: transaction ,commandTimeout: commandTimeout)
                .ToList();
        }

     
     
        /// <summary>
        /// Inserts an entity into table "Ts" and returns identity id.
        /// </summary>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToInsert">Entity to insert</param>
        /// <returns>Identity of inserted entity</returns>
        public static long Insert<T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = entityToInsert.GetType();
            var name = ToStr.GetTableName(type);
            var sbColumnList = new StringBuilder(null);
			var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);
			var computedProperties = ComputedPropertiesCache(type);
            #region 自增主键注释
            //排除主键
            //var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties));
            //for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
            //{
            //    var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
            //    sbColumnList.AppendFormat("[{0}]", camelCase2_(property.Name));
            //    if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
            //        sbColumnList.Append(", ");
            //}
            //var sbParameterList = new StringBuilder(null);
            //for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
            //{
            //    var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
            //    sbParameterList.AppendFormat("@{0}", property.Name);
            //    if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
            //        sbParameterList.Append(", ");
            //}
            #endregion
            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
				sbColumnList.AppendFormat("[{0}]",ToStr.camelCase2_(property.Name));
                if (i < allProperties.Count() - 1)
					sbColumnList.Append(", ");
            }
			var sbParameterList = new StringBuilder(null);
			for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                sbParameterList.AppendFormat("@{0}", property.Name);
                if (i < allProperties.Count() - 1)
                    sbParameterList.Append(", ");
            }
			ISqlAdapter adapter = GetFormatter(connection);
			int id = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(), sbParameterList.ToString(),  keyProperties, entityToInsert);
			return id;
        }
        /// <summary>
        /// Inserts an entity into table "Ts" and returns identity id.
        /// </summary>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToInsert">Entity to insert</param>
        /// <returns>Identity of inserted entity</returns>
        public static long InsertNoNull<T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null) where T : class {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var sbColumnList = new StringBuilder(null);
            IEnumerable<PropertyInfo> allProperties = TypePropertiesCache(type);
            IEnumerable<PropertyInfo> keyProperties = KeyPropertiesCache(type);
            List<PropertyInfo> notNullProperties = new List<PropertyInfo>();
            for (var i = 0; i < allProperties.Count(); i++) {
                PropertyInfo property = allProperties.ElementAt(i);
                var obj = property.GetValue(entityToInsert,null);
                if (obj == null) {
                    continue;
                }
                if(property.PropertyType == typeof(DateTime)) {
                    if (((DateTime)obj).Ticks == DateTime.MinValue.Ticks) {
                        continue;
                    }
                }
                notNullProperties.Add(property);
                if (notNullProperties.Count() != 1) {
                    sbColumnList.Append(", ");
                }
                sbColumnList.AppendFormat("[{0}]",ToStr.camelCase2_(property.Name));
            }
            var sbParameterList = new StringBuilder(null);
            for (var i = 0; i < notNullProperties.Count(); i++) {
                var property = notNullProperties.ElementAt(i);
                sbParameterList.AppendFormat("@{0}", property.Name);
                if (i < notNullProperties.Count() - 1)
                    sbParameterList.Append(", ");
            }
            ISqlAdapter adapter = GetFormatter(connection);
            int id = adapter.Insert(connection, transaction, commandTimeout, name, sbColumnList.ToString(), sbParameterList.ToString(), keyProperties, entityToInsert);
            return id;
        }
        /// <summary>
        /// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
        /// </summary>
        /// <typeparam name="T">Type to be updated</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToUpdate">Entity to be updated</param>
        /// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
        /// 
         public static bool updateField<T>(this IDbConnection connection ,T entityToUpdate , IDbTransaction transaction = null ,params String[] filedNames) where T : class {
            
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set " ,name);
          
            for( var i = 0;i < filedNames.Count();i++ ) {
                var property = filedNames[i];
                sb.AppendFormat("{0} = @{1} , " ,ToStr.camelCase2_(property) ,property);
            }
            sb.Append(" id=id where id=@id");
            var updated = connection.Execute(sb.ToString() ,entityToUpdate ,commandTimeout: null ,transaction: transaction);
            return updated > 0;
        }
         public static  bool updateNotNull<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var proxy = entityToUpdate as IProxy;
            if (proxy != null)
            {
                if (!proxy.IsDirty) return false;
            }
            var type = entityToUpdate.GetType();
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set ", name);
            var allProperties = TypePropertiesCache(type);
            for (var i = 0; i < allProperties.Count(); i++)
            {
                var property = allProperties.ElementAt(i);
                if (property.Name.ToLower() == "id") {
                    continue;
                }
                var obj = property.GetValue(entityToUpdate,null);
                if (obj == null) {
                    continue;
                }
                if(property.PropertyType == typeof(DateTime)) {
                    if (((DateTime)obj).Ticks == DateTime.MinValue.Ticks) {
                        continue;
                    }
                }
                if( property.PropertyType == typeof(int)|| property.PropertyType == typeof(long) ) {
                    if( Convert.ToInt32( obj) == 0 ) {
                        continue;
                    }
                }
                sb.AppendFormat("{0} = @{1} , ",ToStr.camelCase2_(property.Name), property.Name);
            }
            sb.Append(" id=id where id=@id");
            var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);
            return updated > 0;
        }
        /// <summary>
        /// Updates entity in table "Ts", checks if the entity is modified if the entity is tracked by the Get() extension.
        /// </summary>
        /// <typeparam name="T">Type to be updated</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToUpdate">Entity to be updated</param>
        /// <returns>true if updated, false if not found or not modified (tracked entities)</returns>
        public static bool Update<T>(this IDbConnection connection, T entityToUpdate, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var proxy = entityToUpdate as IProxy;
            if (proxy != null)
            {
                if (!proxy.IsDirty) return false;
            }
            var type = entityToUpdate.GetType();
            var keyProperties = KeyPropertiesCache(type);
            if (!keyProperties.Any())
                throw new ArgumentException("Entity must have at least one [Key] property");
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set ", name);
            var allProperties = TypePropertiesCache(type);
            var computedProperties = ComputedPropertiesCache(type);
            var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties));
            for (var i = 0; i < nonIdProps.Count(); i++)
            {    var property = nonIdProps.ElementAt(i);
                 if (property.Name.ToLower() == "id") {
                    continue;
                }
                 var obj = property.GetValue(entityToUpdate,null);
                 if(property.PropertyType == typeof(DateTime)) {
                    if (((DateTime)obj).Ticks == DateTime.MinValue.Ticks) {
                        continue;
                    }
                }
                sb.AppendFormat("{0} = @{1} , ",ToStr.camelCase2_(property.Name), property.Name);
            }
             sb.AppendFormat("id = id where ");
            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("{0} = @{1}", property.Name, property.Name);
                if (i < keyProperties.Count() - 1)
                    sb.AppendFormat(" and ");
            }
            var updated = connection.Execute(sb.ToString(), entityToUpdate, commandTimeout: commandTimeout, transaction: transaction);
            return updated > 0;
        }
        /// <summary>
        /// Delete entity in table "Ts".
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToDelete">Entity to delete</param>
        /// <returns>true if deleted, false if not found</returns>
        public static bool Delete<T>(this IDbConnection connection, T entityToDelete, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
			if (entityToDelete == null)
				throw new ArgumentException("Cannot Delete null Object", "entityToDelete");
            var type = typeof(T);
            var keyProperties = KeyPropertiesCache(type);
            if (keyProperties.Count() == 0)
                throw new ArgumentException("Entity must have at least one [Key] property");
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where ", name);
            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("{0} = @{1}", property.Name, property.Name);
                if (i < keyProperties.Count() - 1)
                    sb.AppendFormat(" and ");
            }
            var deleted = connection.Execute(sb.ToString(), entityToDelete, transaction: transaction, commandTimeout: commandTimeout);
            return deleted > 0;
        }
        public static int BatchInsert<T>(this IDbConnection connection,List<T> entities ,string[] exclude = null,IDbTransaction transaction = null) {
            if( exclude == null || exclude.Length <= 0 )
                exclude = new[] { "Tid" };
            for( var i = 0;i < exclude.Length;i++ ) {
                exclude[i] = exclude[i].ToLower();
            }
            if( entities != null && entities.Count > 0 ) {
                var tbname = ToStr.GetTableName(typeof(T));
                var colms = new List<string>();
                var ps = entities.First().GetType().GetProperties().ToList();
                foreach( var p in ps ) {
                    colms.Add(ToStr.camelCase2_(p.Name));
                }
                var paramList = new DynamicParameters();
                var paramsNames = new List<string>();
                var s = 0;
                var n = "a";
                foreach( var item in entities ) {
                    var toNames = new List<string>();
                    foreach( var p in ps ) {
                        var pname = $"@{n}{s}";
                        var pvalue = p.GetValue(item ,null);
                        if( pvalue!=null&& pvalue is DateTime ) {
                            if( (( DateTime ) pvalue).Year == 1 ) {
                                pvalue = null;
                            }
                        }
                        toNames.Add(pname);
                        paramList.Add(pname ,pvalue);
                        s++;
                    }
                    paramsNames.Add($"({string.Join("," ,toNames)})");
                }
                var sql = $"INSERT INTO {tbname} ({string.Join("," ,colms)}) VALUES {string.Join("," ,paramsNames)}";
                return connection.Execute(sql ,paramList ,transaction);
            }
            throw new Exception("传入参数先");
        }

        public static int BatchUpdate<T>(this IDbConnection connection ,List<T> entities ,string tbname,params String[] propertys) {
            if( entities == null || entities.Count == 0 ) {
                return 0;
            }
            List<PropertyInfo> psAll = entities.First().GetType().GetProperties().ToList();
            List<PropertyInfo> ps = new List<PropertyInfo>();
            var colms = new List<string>();
            PropertyInfo idInfo = null;
            foreach( var p in psAll ) {
                if( propertys.Contains(p.Name) ) {
                    ps.Add(p);
                    colms.Add(ToStr.camelCase2_(p.Name));
                }

                if( p.Name=="id" ) {
                    idInfo = p;
                }
            }
            if( idInfo == null ) {
                throw new Exception("无id不能更新");
            }

            var paramList = new DynamicParameters();
            var s = 0;
            var sqls = new List<string>();
            foreach( var entity in entities ) {
                var sets = new List<string>();
                for( int i=0;i<ps.Count;i++ ) {
                    var p = ps[i];
                    var pname = $"@a{s}";
                    var pvalue = p.GetValue(entity ,null);
                    paramList.Add(pname ,pvalue);
                    sets.Add($"{colms[i]}={pname}");
                    s++;
                }
                var idValue = idInfo.GetValue(entity ,null);
                paramList.Add($"id{s}" ,idValue);
                sqls.Add( $"update {tbname} set {string.Join("," ,sets)} where id=@id{s}");
                s++;
            }
           
            return connection.Execute(string.Join(";" ,sqls) ,paramList);
          
   
        }
        /// <summary>
        /// Delete all entities in the table related to the type T.
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <returns>true if deleted, false if none found</returns>
        public static bool Delete<T>(this IDbConnection connection, string whereStr, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0} where 1=1 and {1} ", name, whereStr);
            var deleted = connection.Execute(statement, null, transaction: transaction, commandTimeout: commandTimeout);
            return deleted > 0;
        }
         
          public static bool Delete<T>(this IDbConnection connection, long id, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
           return delete( connection,  type,  id,  transaction,  commandTimeout);
        }
         public static bool delete(this IDbConnection connection, BaseEntity e, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            Type type = e.GetType();
            return delete(connection, type, e.id, transaction, commandTimeout);
         }
        public static bool delete(this IDbConnection connection, Type type ,long id, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0} where id={1} ", name, id);
            var deleted = connection.Execute(statement, null, transaction: transaction, commandTimeout: commandTimeout);
            return deleted > 0;
         }
       
         public static int  RunProcedure(this IDbConnection connection ,String proc ,DynamicParameters param) {
         return    connection.Execute(proc,param ,null ,null ,CommandType.StoredProcedure);
        }
        /// <summary>
        /// Delete all entities in the table related to the type T.
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <returns>true if deleted, false if none found</returns>
        public static bool DeleteAll<T>(this IDbConnection connection, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
        {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0}", name);
            var deleted = connection.Execute(statement, null, transaction: transaction, commandTimeout: commandTimeout);
            return deleted > 0;
        }
		public static ISqlAdapter GetFormatter(IDbConnection connection)
		{
			string name = connection.GetType().Name.ToLower();
			if (!AdapterDictionary.ContainsKey(name))
				return new SqlServerAdapter();
			return AdapterDictionary[name];
		}
    	class ProxyGenerator
        {
            private static readonly Dictionary<Type, object> TypeCache = new Dictionary<Type, object>();
            private static AssemblyBuilder GetAsmBuilder(string name)
            {
                var assemblyBuilder = Thread.GetDomain().DefineDynamicAssembly(new AssemblyName { Name = name },
                    AssemblyBuilderAccess.Run);       //NOTE: to save, use RunAndSave
                return assemblyBuilder;
            }
            public static T GetInterfaceProxy<T>()
            {
                Type typeOfT = typeof(T);
                object k;
                if (TypeCache.TryGetValue(typeOfT, out k))
                {
                    return (T)k;
                }
                var assemblyBuilder = GetAsmBuilder(typeOfT.Name);
                var moduleBuilder = assemblyBuilder.DefineDynamicModule("SqlMapperExtensions." + typeOfT.Name); //NOTE: to save, add "asdasd.dll" parameter
                var interfaceType = typeof(Dapper.SqlMapperExtensions.IProxy);
                var typeBuilder = moduleBuilder.DefineType(typeOfT.Name + "_" + Guid.NewGuid(),
                    TypeAttributes.Public | TypeAttributes.Class);
                typeBuilder.AddInterfaceImplementation(typeOfT);
                typeBuilder.AddInterfaceImplementation(interfaceType);
                //create our _isDirty field, which implements IProxy
                var setIsDirtyMethod = CreateIsDirtyProperty(typeBuilder);
                // Generate a field for each property, which implements the T
                foreach (var property in typeof(T).GetProperties())
                {
                    var isId = property.GetCustomAttributes(true).Any(a => a is KeyAttribute);
                    CreateProperty<T>(typeBuilder, property.Name, property.PropertyType, setIsDirtyMethod, isId);
                }
                var generatedType = typeBuilder.CreateType();
                //assemblyBuilder.Save(name + ".dll");  //NOTE: to save, uncomment
                var generatedObject = Activator.CreateInstance(generatedType);
                TypeCache.Add(typeOfT, generatedObject);
                return (T)generatedObject;
            }
            private static MethodInfo CreateIsDirtyProperty(TypeBuilder typeBuilder)
            {
                var propType = typeof(bool);
                var field = typeBuilder.DefineField("_" + "IsDirty", propType, FieldAttributes.Private);
                var property = typeBuilder.DefineProperty("IsDirty",
                                               System.Reflection.PropertyAttributes.None,
                                               propType,
                                               new Type[] { propType });
                const MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.NewSlot | MethodAttributes.SpecialName |
                                                    MethodAttributes.Final | MethodAttributes.Virtual | MethodAttributes.HideBySig;
                // Define the "get" and "set" accessor methods
                var currGetPropMthdBldr = typeBuilder.DefineMethod("get_" + "IsDirty",
                                             getSetAttr,
                                             propType,
                                             Type.EmptyTypes);
                var currGetIL = currGetPropMthdBldr.GetILGenerator();
                currGetIL.Emit(OpCodes.Ldarg_0);
                currGetIL.Emit(OpCodes.Ldfld, field);
                currGetIL.Emit(OpCodes.Ret);
                var currSetPropMthdBldr = typeBuilder.DefineMethod("set_" + "IsDirty",
                                             getSetAttr,
                                             null,
                                             new Type[] { propType });
                var currSetIL = currSetPropMthdBldr.GetILGenerator();
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldarg_1);
                currSetIL.Emit(OpCodes.Stfld, field);
                currSetIL.Emit(OpCodes.Ret);
                property.SetGetMethod(currGetPropMthdBldr);
                property.SetSetMethod(currSetPropMthdBldr);
                var getMethod = typeof(Dapper.SqlMapperExtensions.IProxy).GetMethod("get_" + "IsDirty");
                var setMethod = typeof(Dapper.SqlMapperExtensions.IProxy).GetMethod("set_" + "IsDirty");
                typeBuilder.DefineMethodOverride(currGetPropMthdBldr, getMethod);
                typeBuilder.DefineMethodOverride(currSetPropMthdBldr, setMethod);
                return currSetPropMthdBldr;
            }
            private static void CreateProperty<T>(TypeBuilder typeBuilder, string propertyName, Type propType, MethodInfo setIsDirtyMethod, bool isIdentity)
            {
                //Define the field and the property 
                var field = typeBuilder.DefineField("_" + propertyName, propType, FieldAttributes.Private);
                var property = typeBuilder.DefineProperty(propertyName,
                                               System.Reflection.PropertyAttributes.None,
                                               propType,
                                               new Type[] { propType });
                const MethodAttributes getSetAttr = MethodAttributes.Public | MethodAttributes.Virtual |
                                                    MethodAttributes.HideBySig;
                // Define the "get" and "set" accessor methods
                var currGetPropMthdBldr = typeBuilder.DefineMethod("get_" + propertyName,
                                             getSetAttr,
                                             propType,
                                             Type.EmptyTypes);
                var currGetIL = currGetPropMthdBldr.GetILGenerator();
                currGetIL.Emit(OpCodes.Ldarg_0);
                currGetIL.Emit(OpCodes.Ldfld, field);
                currGetIL.Emit(OpCodes.Ret);
                var currSetPropMthdBldr = typeBuilder.DefineMethod("set_" + propertyName,
                                             getSetAttr,
                                             null,
                                             new Type[] { propType });
                //store value in private field and set the isdirty flag
                var currSetIL = currSetPropMthdBldr.GetILGenerator();
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldarg_1);
                currSetIL.Emit(OpCodes.Stfld, field);
                currSetIL.Emit(OpCodes.Ldarg_0);
                currSetIL.Emit(OpCodes.Ldc_I4_1);
                currSetIL.Emit(OpCodes.Call, setIsDirtyMethod);
                currSetIL.Emit(OpCodes.Ret);
                //TODO: Should copy all attributes defined by the interface?
                if (isIdentity)
                {
                    var keyAttribute = typeof(KeyAttribute);
                    var myConstructorInfo = keyAttribute.GetConstructor(new Type[] { });
                    var attributeBuilder = new CustomAttributeBuilder(myConstructorInfo, new object[] { });
                    property.SetCustomAttribute(attributeBuilder);
                }
                property.SetGetMethod(currGetPropMthdBldr);
                property.SetSetMethod(currSetPropMthdBldr);
                var getMethod = typeof(T).GetMethod("get_" + propertyName);
                var setMethod = typeof(T).GetMethod("set_" + propertyName);
                typeBuilder.DefineMethodOverride(currGetPropMthdBldr, getMethod);
                typeBuilder.DefineMethodOverride(currSetPropMthdBldr, setMethod);
            }
        }
    }
    [AttributeUsage(AttributeTargets.Class)]
    public class TableAttribute : Attribute
    {
        public TableAttribute(string tableName)
        {
            Name = tableName;
        }
        public string Name { get; private set; }
    }
    // do not want to depend on data annotations that is not in client profile
    [AttributeUsage(AttributeTargets.Property)]
    public class KeyAttribute : Attribute
    {
    }
	[AttributeUsage(AttributeTargets.Property)]
	public class WriteAttribute : Attribute
	{
		public WriteAttribute(bool write)
        {
			Write = write;
        }
        public bool Write { get; private set; }
	}
	[AttributeUsage(AttributeTargets.Property)]
	public class ComputedAttribute : Attribute
	{
	}
}
public interface ISqlAdapter
{
	int Insert(IDbConnection connection, IDbTransaction transaction, int? commandTimeout, String tableName, string columnList, string parameterList, IEnumerable<PropertyInfo> keyProperties, object entityToInsert);
}
public class SqlServerAdapter : ISqlAdapter
{
	public int Insert(IDbConnection connection, IDbTransaction transaction, int? commandTimeout, String tableName, string columnList, string parameterList, IEnumerable<PropertyInfo> keyProperties, object entityToInsert)
	{
        string cmd = String.Format("insert into {0} ({1}) values ({2}) ", tableName, columnList, parameterList);
		int id=connection.ExecuteScalar<int>(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout); 
		
        
        //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
		//var r = connection.Query("select @@IDENTITY id", transaction: transaction, commandTimeout: commandTimeout);
		//int id = (int)r.First().id;
		//if (keyProperties.Any())
		//	keyProperties.First().SetValue(entityToInsert, id, null);
		return id;
	}
}
public class PostgresAdapter : ISqlAdapter
{
	public int Insert(IDbConnection connection, IDbTransaction transaction, int? commandTimeout, String tableName, string columnList, string parameterList, IEnumerable<PropertyInfo> keyProperties, object entityToInsert)
	{
		StringBuilder sb = new StringBuilder();
		sb.AppendFormat("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);
		// If no primary key then safe to assume a join table with not too much data to return
		if (!keyProperties.Any())
			sb.Append(" RETURNING *");
		else
		{
			sb.Append(" RETURNING ");
			bool first = true;
			foreach (var property in keyProperties)
			{
				if (!first)
					sb.Append(", ");
				first = false;
				sb.Append(property.Name);
			}
		}
		var results = connection.Query(sb.ToString(), entityToInsert, transaction: transaction, commandTimeout: commandTimeout);
		// Return the key by assinging the corresponding property in the object - by product is that it supports compound primary keys
		int id = 0;
		foreach (var p in keyProperties)
		{
			var value = ((IDictionary<string, object>)results.First())[p.Name.ToLower()];
			p.SetValue(entityToInsert, value, null);
			if (id == 0)
				id = Convert.ToInt32(value);
		}
		return id;
	}
}
public class SQLiteAdapter : ISqlAdapter
{
	public int Insert(IDbConnection connection, IDbTransaction transaction, int? commandTimeout, String tableName, string columnList, string parameterList, IEnumerable<PropertyInfo> keyProperties, object entityToInsert)
	{
		string cmd = String.Format("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);
		connection.Execute(cmd, entityToInsert, transaction: transaction, commandTimeout: commandTimeout);
		var r = connection.Query("select last_insert_rowid() id", transaction: transaction, commandTimeout: commandTimeout);
		int id = (int)r.First().id;
		if (keyProperties.Any())
			keyProperties.First().SetValue(entityToInsert, id, null);
		return id;
	}
}
